"""
Case Type   : DISTINCT
Case Name   : 导入数据t_Ustore_DQL_Distinct_type_000
Create At   : 2022-02-26
Owner       : wang_zhengyuan
Description :
    1、设置参数enable_copy_server_files参数值为on
    2、gsql连接数据库，创建表
    3、使用copy命令插入数据
    4、创建索引
    5、环境清理
Expect      :
    1、参数值修改成功
    2、gsql连接数据库，创建表成功
    3、数据插入成功
    4、索引创建成功
    5、清理成功
History     :
"""

import os
import unittest
import subprocess

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro
from yat.test import Node


class Opengauss_Function_Ustore_DQL_Distinct_Case0002(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = CommonSH('PrimaryRoot')
        self.com = Common()
        self.constant = Constant()
        self.t_name1 = 't_Ustore_DQL_Distinct_type_000'
        self.i_name_list = []
        for i in range(0, 12):
            self.i_name_list.append(f"i_Ustore_DQL_Distinct_Case_type_000_{i}")
        self.copy_file1 = 'vecctor_type_tab'
        self.cur_path_1 = os.path.join(macro.SCRIPTS_PATH, self.copy_file1)
        self.target_path = macro.DB_INSTANCE_PATH
        self.tar_path_1 = os.path.join(macro.DB_INSTANCE_PATH, self.copy_file1)

    def test_main(self):
        step_txt = '----step1:修改参数值，expect: 参数值修改成功----'
        self.log.info(step_txt)
        self.log.info('---------------获取并打印参数初始值------------------')
        self.show_copy_pram = f'show enable_copy_server_files;'
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.pram_init = cmd_result.strip().splitlines()[-2].strip()

        self.log.info('---------------开始修改参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                  "enable_copy_server_files=on")

        self.log.info('---------------检查参数是否修改成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value1 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual('on', self.cmd_result_value1, "参数修改失败")

        step_txt = '----step2:创建表，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop table if exists {self.t_name1};\
            create table {self.t_name1} (\
              col_tinyint       tinyint,\
              col_smallint      smallint,\
              col_int        integer,\
              col_bigint       bigint,\
              col_money            varchar(1000),\
              col_numeric       numeric,\
              col_real       real,\
              col_double       double precision ,\
              col_decimal          decimal,\
              col_varchar        varchar(1000),\
              col_char       char(30),\
              col_nvarchar2      nvarchar2(1000),\
              col_text       varchar(1000),\
              col_timestamp   varchar(1000),\
              col_timestamptz   timestamp without time zone,\
              col_date       date,\
              col_time       varchar(1000),\
              col_timetz       varchar(1000),\
              col_interval      varchar(1000),\
              col_tinterval        varchar(1000),\
              col_smalldatetine  varchar(1000),\
              col_bytea    bytea,\
              col_boolean    boolean,\
              col_inet    varchar(1000),\
              col_cidr    varchar(1000),\
              col_bit     varchar(100),\
              col_varbit    varchar(1000),\
              col_oid     varchar(1000)\
            ) WITH (STORAGE_TYPE=USTORE);"
        self.log.info(create_sql)
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step3:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(step_txt)
        text = '----step3.1: 获取当前主机的IP地址 expect: 成功----'
        self.log.info(text)
        cmd = "ifconfig -a|grep inet6 -a2|grep broadcast|awk '{print $2}'"
        self.log.info(cmd)
        self.cur_ip = subprocess.getoutput(cmd).strip()
        self.log.info(self.cur_ip)

        text = '----step3.2: 复制文件到数据库主机对应目录 expect: 成功----'
        self.log.info(text)
        if self.cur_ip == self.pri_sh.node.ssh_host:
            self.log.info('当前主机是数据库主机，无需远程复制')
            cmd = f"cp {self.cur_path_1} {self.target_path};"
            self.log.info(cmd)
            res = self.pri_sh.node.sh(cmd).result()
            self.log.info(res)
        else:
            self.com.scp_file(self.pri_sh.node, self.copy_file1,
                              self.target_path)

        text = '----step3.3: 查看数据库主机目标路径下文件 expect: 成功----'
        self.log.info(text)
        cmd = f"ls -l {self.target_path}| grep -E '{self.copy_file1}'"
        self.log.info(cmd)
        res = self.pri_sh.node.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file1}', res, '文件拷贝失败')

        text = '----step3.4:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name1} from '{self.tar_path_1}' " \
            f"delimiters ',';"
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 3621', '执行失败:' + step_txt)

        step_txt = '----step4: 创建索引,expect: 索引创建成功----'
        self.log.info(step_txt)
        column_list = ['col_int', 'col_bigint', 'col_numeric', 'col_double',
                       'col_real', 'col_nvarchar2', 'col_text',
                       'col_timestamp', 'col_date', 'col_time',
            'col_smallint,col_int,col_char,col_text,col_timestamp,col_date',
                       'col_interval']
        for a in range(0, 12):
            create_sql = f"drop index if exists {self.i_name_list[a]} " \
                f"CASCADE;create index {self.i_name_list[a]} " \
                f"on {self.t_name1}({column_list[a]});"
            self.log.info(create_sql)
            create_result = self.pri_sh.execut_db_sql(create_sql)
            self.log.info(create_result)
            self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG,
                          create_result,
                          f'创建{self.i_name_list[a]}失败：' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step5: 环境清理，expect:清理成功----'
        self.log.info(step_txt)
        drop_sql = f'drop table if exists {self.t_name1};'
        self.log.info(drop_sql)
        self.log.info('由于后续用例依赖暂不删表')

        rm_cmd = f"rm -rf {self.tar_path_1} "
        rm_res = self.pri_sh.node.sh(rm_cmd).result()
        self.log.info(rm_res)

        self.log.info('---------------开始还原参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                  f"enable_copy_server_files={self.pram_init}")

        self.log.info('---------------检查参数是否还原成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value2 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual(rm_res, '', '文件删除失败' + step_txt)
        self.assertEqual(self.pram_init, self.cmd_result_value2, "参数还原失败")
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
